package com.proyecto.core.modelo;

import java.io.Serializable;
import java.util.List;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.LockMode;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.proyecto.core.modelo.echo.constantes.TipoBusqueda;
import com.proyecto.core.modelo.excepciones.GeneralException;

public class DAOGenerico extends HibernateDaoSupport {

	protected Class clase;
	
	protected boolean aplicandoUpper = false;
	
	protected static Log log = LogFactory.getLog(DAOGenerico.class);
	
	public DAOGenerico(){
		super();
	}
	

	public void save(Object o) {
		log.debug("saving "+ clase.getSimpleName()+ " instance");
		try {
			getHibernateTemplate().save(o);
			log.debug("save successful");
		} catch (RuntimeException re) {
			log.error("save failed", re);
			throw re;
		}
	}
	public void save(Object o, Session s) {
		log.debug("saving "+ clase.getSimpleName()+ " instance");
		try {
			s.save(o);
			log.debug("save successful");
		} catch (RuntimeException re) {
			log.error("save failed", re);
			throw re;
		}
	}
	
	public void delete(Object o) {
		log.debug("deleting "+ clase.getSimpleName()+ " instance");
		try {
			getHibernateTemplate().delete(o);
			log.debug("delete successful");
		} catch (RuntimeException re) {
			log.error("delete failed", re);
			throw re;
		}
	}
	
	public void delete(Object o, Session s) {
		log.debug("deleting "+ clase.getSimpleName()+ " instance");
		try {
			s.delete(o);
			log.debug("delete successful");
		} catch (RuntimeException re) {
			log.error("delete failed", re);
			throw re;
		}
	}
	
	public Object findById(Object o) {
		log.debug("getting "+clase.getSimpleName()+" instance with id: " + o);
		try {
			Object instance = getHibernateTemplate().get(clase, (Serializable) o);
			return instance;
		} catch (RuntimeException re) {
			log.error("get failed", re);
			throw re;
		}
	}
		
	public Object findById(Object o, Session s) {
		log.debug("getting "+clase.getSimpleName()+" instance with id: " + o);
		try {
			Object instance = s.get(clase, (Serializable) o);
			return instance;
		} catch (RuntimeException re) {
			log.error("get failed", re);
			throw re;
		}
	}
		
	//TODO ver bien esto..
	public Object findByIdBloqueando(Object o) {
		Object instance = getHibernateTemplate().get(clase, (Serializable) o,LockMode.READ);
		return instance;
	}
	
	public List findByExample(Object o) {
		log.debug("finding "+clase.getSimpleName()+" instance by example");
		try {
			List results = getHibernateTemplate().findByExample(o);
			log.debug("find by example successful, result size: "
					+ results.size());
			return results;
		} catch (RuntimeException re) {
			log.error("find by example failed", re);
			throw re;
		}
	}
	
	public List findByProperty(String propertyName, Object value) {
		log.debug("finding "+clase.getSimpleName()+" instance with property: " + propertyName
				+ ", value: " + value);
		try {
			String queryString = "from "+clase.getSimpleName()+" as model where model."
					+ propertyName + "= ?";
			return getHibernateTemplate().find(queryString, value);
		} catch (RuntimeException re) {
			log.error("find by property name failed", re);
			throw re;
		}
	}

	public List findByPropertyPorRango(String propertyName, Object value, int limiteInferior, int limiteSuperior) {
		log.debug("finding "+clase.getSimpleName()+" instance with property: " + propertyName
				+ ", value: " + value);
		Session s = null;;
		Transaction t;
		try {
			String queryString = "from "+clase.getSimpleName()+" as model where model."
					+ propertyName + "= ?";
			s = getSessionFactory().openSession();
			t = s.beginTransaction();
			Query query = s.createQuery(queryString);
			List resultados =query.list().subList(limiteInferior, limiteSuperior);
			t.commit();
			return resultados;
		} catch (RuntimeException re) {
			log.error("find by property name failed", re);
			throw re;
		}finally{
			s.close();
			t= null;
			s=null;
		}
	}
	
	public List findAll() {
		log.debug("finding all "+clase.getSimpleName()+" instances");
		try {
			String queryString = "from "+clase.getSimpleName();
			return getHibernateTemplate().find(queryString);
		} catch (RuntimeException re) {
			log.error("find all failed", re);
			throw re;
		}
	}
	
	public Object merge(Object o) {
		log.debug("merging Cargo instance");
		try {
			Object result = o;
			//TODO se puso en comentario porq da error cuando la clase tiene otras clases como atributo
			//Object result = getHibernateTemplate().merge(o);
			getHibernateTemplate().update(o);	
			
			//comentado lo de la session...
//			Object result = 0;
//			Session s = getHibernateTemplate().getSessionFactory().openSession();
//			Transaction t = s.beginTransaction();
//			s.update(o);
//			t.commit();
//			s.close();
			log.debug("merge successful");
			return result;
		} catch (RuntimeException re) {
			log.error("merge failed", re);
			throw re;
		}
	}
	
	public Object merge(Object o, Session s) {
		log.debug("merging Cargo instance");
		try {
			Object result = 0;
			s.update(o);
			log.debug("merge successful");
			return result;
		} catch (RuntimeException re) {
			log.error("merge failed", re);
			throw re;
		}
	}
	
	public void attachDirty(Object o) {
		log.debug("attaching dirty "+clase.getSimpleName()+" instance");
		try {
			getHibernateTemplate().update(o);
			log.debug("attach successful");
		} catch (RuntimeException re) {
			log.error("attach failed", re);
			throw re;
		}
	}
	
	public void attachClean(Object o) {
		log.debug("attaching clean Cargo instance");
		try {
			getHibernateTemplate().lock(o, LockMode.NONE);
			log.debug("attach successful");
		} catch (RuntimeException re) {
			log.error("attach failed", re);
			throw re;
		}
	}
	


	public List buscarPorPropiedad(String[] atributo, Object[] valor, int tipoBusqueda,boolean ... aplicarUpper) {
		try {
			String queryString = this.armarQuery(atributo, valor, tipoBusqueda,aplicarUpper);
			//System.out.println("buscarPorPropiedad.queryString:: " + queryString);
			return getHibernateTemplate().find(queryString,valor);
		} catch (RuntimeException re) {
			re.printStackTrace();
//			re.printStackTrace();
			throw re;
		}
	}
	
	public Integer buscarPorPropiedadCount(String[] atributo, Object[] valor, int tipoBusqueda,boolean ... aplicarUpper) {
		Session s = null;
		Transaction t = null;
		try {
			String queryString = this.armarQueryCount(atributo, valor, tipoBusqueda,aplicarUpper);
			System.out.println("queryString-> " + queryString);
			s = getSessionFactory().openSession();
			t = s.beginTransaction();
			Query query = s.createQuery(queryString);
			List resultado = query.list();
			t.commit();
			return Integer.parseInt(resultado.get(0).toString());
		} catch (RuntimeException re) {
			re.printStackTrace();
//			re.printStackTrace();
			throw re;
		}finally{
			s.close();
			t=null;
			s=null;
		}
	}

	public Integer buscarPorPropiedadCount(String[] atributo, Object[] valor, int tipoBusqueda, int condicionFiltro, boolean ... aplicarUpper) {
		Session s = null;
		Transaction t = null;
		try {
			String queryString = this.armarQueryCount(atributo, valor, tipoBusqueda,condicionFiltro,aplicarUpper);
			//System.out.println("buscarPorPropiedadCount.queryString: " + queryString);
			s = getSessionFactory().openSession();
			t = s.beginTransaction();
			Query query = s.createQuery(queryString);
			List resultado = query.list();
			t.commit();
			return Integer.parseInt(resultado.get(0).toString());
		} catch (RuntimeException re) {
			re.printStackTrace();
//			re.printStackTrace();
			throw re;
		}finally{
			s.close();
			t=null;
			s=null;
		}
	}

	public Integer buscarPorPropiedadCountEspecialMedicamento(String[] atributo, Object[] valor, int tipoBusqueda, int condicionFiltro, boolean ... aplicarUpper) {
		Session s = null;
		Transaction t = null;
		try {
			String queryString = this.armarQueryCountEspecialMedicamento(atributo, valor, tipoBusqueda,condicionFiltro,aplicarUpper);
			//System.out.println("buscarPorPropiedadCount.queryString: " + queryString);
			s = getSessionFactory().openSession();
			t = s.beginTransaction();
			Query query = s.createQuery(queryString);
			List resultado = query.list();
			t.commit();
			return Integer.parseInt(resultado.get(0).toString());
		} catch (RuntimeException re) {
			re.printStackTrace();
//			re.printStackTrace();
			throw re;
		}finally{
			s.close();
			t=null;
			s=null;
		}
	}
	
	public List buscarPorPropiedadPorRangos(String[] atributo, Object[] valor,int limiteInferior, int limiteSuperior, int tipoBusqueda, boolean ... aplicarUpper) {
		Session s = getSessionFactory().openSession();
		Transaction t = null;
		try {
			String queryString = this.armarQuery2(atributo, valor, tipoBusqueda,aplicarUpper);
			t = s.beginTransaction();
			Query query = s.createQuery(queryString);
			query.setFirstResult((limiteSuperior-limiteInferior==0)?(limiteInferior-1):limiteInferior);
			query.setMaxResults((limiteSuperior-limiteInferior!=0)?(limiteSuperior-limiteInferior):1);
			List resultados = query.list();
			t.commit();
			return resultados;
		} catch (RuntimeException re) {
			re.printStackTrace();
			throw re;
		}finally{
			s.close();
			s = null;
			t = null;
		}
	}


	public List buscarPorPropiedadPorRangos(String[] atributo, Object[] valor,int limiteInferior, int limiteSuperior, 
				int tipoBusqueda, int condicionFiltro, boolean ... aplicarUpper) {
		Session s = getSessionFactory().openSession();
		Transaction t = null;
		try {
			String queryString = this.armarQuery2(atributo, valor, tipoBusqueda,condicionFiltro,aplicarUpper);
			System.out.println("buscarPorPropiedadPorRangos.queryString: " + queryString);
			t = s.beginTransaction();
			Query query = s.createQuery(queryString);
			query.setFirstResult((limiteSuperior-limiteInferior==0)?(limiteInferior-1):limiteInferior);
			query.setMaxResults((limiteSuperior-limiteInferior!=0)?(limiteSuperior-limiteInferior):1);
			List resultados = query.list();
			t.commit();
			return resultados;
		} catch (RuntimeException re) {
			re.printStackTrace();
			throw re;
		}finally{
			s.close();
			s = null;
			t = null;
		}
	}
	
	public List buscarPorPropiedadPorRangosEspecialMedicamento(String[] atributo, Object[] valor,int limiteInferior, int limiteSuperior, 
			int tipoBusqueda, int condicionFiltro, boolean ... aplicarUpper) {
	Session s = getSessionFactory().openSession();
	Transaction t = null;
	try {
			String queryString = this.armarQuery3(atributo, valor, tipoBusqueda,condicionFiltro,aplicarUpper);
			System.out.println("buscarPorPropiedadPorRangos.queryString: " + queryString);
			t = s.beginTransaction();
			Query query = s.createQuery(queryString);
			query.setFirstResult((limiteSuperior-limiteInferior==0)?(limiteInferior-1):limiteInferior);
			query.setMaxResults((limiteSuperior-limiteInferior!=0)?(limiteSuperior-limiteInferior):1);
			List resultados = query.list();
			t.commit();
			return resultados;
		} catch (RuntimeException re) {
			re.printStackTrace();
			throw re;
		}finally{
			s.close();
			s = null;
			t = null;
		}
	}
	public List buscarPorPropiedadMultiPase(String[] atributo, Object[] valor, int[] tipoBusqueda,boolean ... aplicarUpper) {
		try {
			//*
			String queryString = this.armarQueryMultiPase(atributo, valor, tipoBusqueda,aplicarUpper);
			return getHibernateTemplate().find(queryString,valor);
		} catch (RuntimeException re) {
			re.printStackTrace();
//			re.printStackTrace();
			throw re;
		}
	}

	
	public Integer buscarPorPropiedadMultiPaseCount(String[] atributo, Object[] valor, int[] tipoBusqueda,boolean ... aplicarUpper) {
		Session s = null;
		Transaction t = null;
		try {
			String queryString = this.armarQueryMultiPaseCount(atributo, valor, tipoBusqueda,aplicarUpper);
			s = getSessionFactory().openSession();
			t = s.beginTransaction();
			Query query = s.createQuery(queryString);
			List resultado = query.list();
			t.commit();
			return Integer.parseInt(resultado.get(0).toString());
		} catch (RuntimeException re) {
			re.printStackTrace();
//			re.printStackTrace();
			throw re;
		}finally{
			s.close();
			t=null;
			s=null;
		}
	}
	
	public List countBusqueda(String[] atributo, Object[] valor, int tipoBusqueda, boolean... aplicarUpper) {

		try {
			String queryString = "select count(*) "+this.armarQuery(atributo, valor, tipoBusqueda);
			return getHibernateTemplate().find(queryString,valor);
				
		} catch (RuntimeException re) {
			throw re;
		}
	}

	public List countBusqueda(String[] atributo, Object[] valor, int tipoBusqueda, int condicionFiltro, boolean... aplicarUpper) {

		try {
			String queryString = "select count(*) "+this.armarQuery2(atributo, valor, condicionFiltro, tipoBusqueda);
			return getHibernateTemplate().find(queryString,valor);
				
		} catch (RuntimeException re) {
			throw re;
		}
	}


	public List countBusquedaEspecialMedicamento(String[] atributo, Object[] valor, int tipoBusqueda, int condicionFiltro, boolean... aplicarUpper) {

		try {
			String queryString = "select count(*) "+this.armarQuery3(atributo, valor, condicionFiltro, tipoBusqueda);
			return getHibernateTemplate().find(queryString,valor);
				
		} catch (RuntimeException re) {
			throw re;
		}
	}
	
	public List maxBusqueda(String[] atributo, Object[] valor, String atributoMax, int tipoBusqueda, boolean... aplicarUpper) {

		try {
			String queryString = "select max(" + atributoMax+ ") " + this.armarQuery(atributo, valor, tipoBusqueda);
			//System.out.println("maxBusqueda.queryString: " + queryString);
			return getHibernateTemplate().find(queryString,valor);
				
		} catch (RuntimeException re) {
			throw re;
		}
	}
	
	public Class getClase() {
		return clase;
	}

	public void setClase(Class clase) {
		this.clase = clase;
	}
	
	private String armarQuery(String[] atributo, Object[] valor, int tipoBusqueda, boolean ... aplicarUpper){
		if (aplicarUpper != null && aplicarUpper.length > 0){
			aplicandoUpper = aplicarUpper[0];
		}
		String queryString = "from "+clase.getSimpleName()+" as model where ";
		//System.out.println("queryString armarQuery:: " + queryString);
		for (int i = 0; i < atributo.length; i++) {
			String at = atributo[i];
			Object val = valor[i];
			if (val instanceof String){	
			
				if (TipoBusqueda.LIKE_EMPIECE_POR == tipoBusqueda){
					if (aplicandoUpper){
						queryString = queryString + "upper (model."+ at + ") LIKE upper (?) ";	
					}else{
						queryString = queryString + " model.id.ciaopr = '1' and model."+ at + " LIKE ?  ";	
					}					
					String temp = val.toString() + "%";
					valor[i] = temp;
				}else if(TipoBusqueda.LIKE_CONTENGA_A == tipoBusqueda){
					if (aplicandoUpper){
						queryString = queryString + "upper (model."+ at + ") LIKE upper (?) ";		
					}else{
						queryString = queryString + " model.id.ciaopr = '1' and  model."+ at + " LIKE ? ";		
					}				
					String temp = "%"+val.toString() + "%";
					valor[i] = temp;							
				}else{
					//Por defecto busca con el operador igual..
					if (aplicandoUpper){
						queryString = queryString + "upper (model."+ at + ") = upper (?) ";	
					}else{
						queryString = queryString + " model."+ at + " = ? ";
					}			
				}
				
				
			}else {
				//es Integer o Double, OJO: verificar el Double..
				queryString = queryString + "model."+ at + " = ? ";				
			}
			
			if ((i+1)!= atributo.length ){
				//hay mas de uno..
				queryString = queryString + " AND ";
			}
		}
		
		aplicandoUpper = false; // por si acaso
		return queryString;
	}

	/*
	 * LA CONSTRUCCION DE ESTA CONSULTA ES ESPECIAL
	 * se requiere que en el sql se le pueda incluir varias condiciones donde se debe consultar
	 * SEXOCOD con 2 datos al tiempo, (tener estos 2 datos al tiempo es lo que radica la modificacion en la construccion del sql estandar)
	 * ADICIONALMENTE
	 * EDAD EN UN between...
	 * CONSTRUIRE ESTO ACORDE A ATRIBUTOS ESPECIFICOS Y CON HUBICACION FIJA.
	 * COMO DIGO este metodo se sale de los estandares.
	 * atributos para el catalogo * 0:sexocod * 1:sexocod * 2:edadhc *3:en adelante los atributos que vengan.
	 * 
	 */
	
	private String armarQuery(String[] atributo, Object[] valor, int tipoBusqueda, int condicionFiltro, boolean ... aplicarUpper){
		if (aplicarUpper != null && aplicarUpper.length > 0){
			aplicandoUpper = aplicarUpper[0];
		}
		String queryString = "from "+clase.getSimpleName()+" as model where ";
		//System.out.println("queryString armarQuery:: " + queryString);
		for (int i = 0; i < atributo.length; i++) {
			String at = atributo[i];
			Object val = valor[i];
			Object valTemp2 = valor[2];
			if (val instanceof String){	
			
				if (TipoBusqueda.LIKE_EMPIECE_POR == tipoBusqueda){
					if (aplicandoUpper){
						queryString = queryString + "upper (model."+ at + ") LIKE upper (?) ";	
					}else{
						queryString = queryString + " model.id.ciaopr = '1' and model."+ at + " LIKE ?  ";	
					}					
					String temp = val.toString() + "%";
					valor[i] = temp;
				}else if(TipoBusqueda.LIKE_CONTENGA_A == tipoBusqueda){
					if (aplicandoUpper){
						if(valTemp2.toString() == ""){
							queryString = queryString + "upper (model."+ at + ") LIKE upper (?) ";	
						}else if(valTemp2.toString() != ""){
							if(i==0){
								
							}else{
								queryString = queryString + "upper (model."+ at + ") LIKE upper (?) ";	
							}
						}
					}else{
						if(valTemp2.toString() == ""){
							queryString = queryString + " model.id.ciaopr = '1' and  model."+ at + " LIKE ? ";	
						}else if(valTemp2.toString() != ""){
							if(i==0){
								
							}else{
								queryString = queryString + " model.id.ciaopr = '1' and  model."+ at + " LIKE ?";	
							}
						}
								
					}				
					String temp = "%"+val.toString() + "%";
					valor[i] = temp;							
				}else{
					//Por defecto busca con el operador igual..
					if (aplicandoUpper){
						queryString = queryString + "upper (model."+ at + ") = upper (?) ";	
					}else{
						queryString = queryString + " model."+ at + " = ? ";
					}			
				}
				
				
			}else {
				//es Integer o Double, OJO: verificar el Double..
				queryString = queryString + "model."+ at + " = ? ";				
			}
			

			if ((i+1)!= atributo.length ){
				//hay mas de uno..
				if(condicionFiltro == 0 ){
					queryString = queryString + " AND ";
				}else{
					if((i+2)== atributo.length ){
						queryString = queryString + " AND ";
						//i = atributo.length+1;
					}else{
						if(valTemp2.toString() == ""){
							queryString = queryString + " OR ";	
						}else if(valTemp2.toString() != ""){
							if(i==0){
								
							}else{
								queryString = queryString + " OR ";	
							}
						}
							
					}
				}
			}
		}
		
		aplicandoUpper = false; // por si acaso
		return queryString;
	}
	private String armarQuery2(String[] atributo, Object[] valor, int tipoBusqueda, boolean ... aplicarUpper){
		if (aplicarUpper != null && aplicarUpper.length > 0){
			aplicandoUpper = aplicarUpper[0];
		}
		String queryString = "from "+clase.getSimpleName()+" as model where ";
		for (int i = 0; i < atributo.length; i++) {
			String at = atributo[i];
			Object val = valor[i];
			if (val instanceof String){	
			
				if (TipoBusqueda.LIKE_EMPIECE_POR == tipoBusqueda){
					if (aplicandoUpper){
						queryString = queryString + "upper (model."+ at + ") LIKE upper ('"+val.toString()+"%') ";	
					}else{
						queryString = queryString + " model.id.ciaopr = '1' and model."+ at + " LIKE '"+val.toString()+"%'  ";	
					}					
				}else if(TipoBusqueda.LIKE_CONTENGA_A == tipoBusqueda){
					if (aplicandoUpper){
						queryString = queryString + "upper (model."+ at + ") LIKE upper ('%"+val.toString()+"%') ";		
					}else{
						queryString = queryString + " model.id.ciaopr = '1' and  model."+ at + " LIKE '%"+val.toString()+"%' ";		
					}			
				}else{
					//Por defecto busca con el operador igual..
					if (aplicandoUpper){
						queryString = queryString + "upper (model."+ at + ") = upper ('"+val.toString()+"') ";	
					}else{
						queryString = queryString + " model."+ at + " = '"+val.toString()+"' ";
					}			
				}
				
				
			}else {
				//es Integer o Double, OJO: verificar el Double..
				queryString = queryString + "model."+ at + " = "+val+" ";				
			}
			
			if ((i+1)!= atributo.length ){
				//hay mas de uno..
				queryString = queryString + " AND ";
				
			}
		}
		
		aplicandoUpper = false; // por si acaso
		return queryString;
	}


	private String armarQuery2(String[] atributo, Object[] valor, int tipoBusqueda, int condicionFiltro, boolean ... aplicarUpper){
		if (aplicarUpper != null && aplicarUpper.length > 0){
			aplicandoUpper = aplicarUpper[0];
		}
		String queryString = "from "+clase.getSimpleName()+" as model where ";
		for (int i = 0; i < atributo.length; i++) {
			String at = atributo[i];
			Object val = valor[i];
			Object valTemp2 = valor[2];
			//System.out.println("i: " + i);
			if (val instanceof String){	
				if(i==0){
					queryString = queryString +"( ";
				}
				if (TipoBusqueda.LIKE_EMPIECE_POR == tipoBusqueda){
					if (aplicandoUpper){
						if(i==2){
							if(condicionFiltro==2){
								queryString = queryString + val + " BETWEEN model.medicamentos.edadmin AND model.medicamentos.edadmax";
							}else{
								queryString = queryString + val + " BETWEEN model.edadmin AND model.edadmax";	
							}
						}else{
							queryString = queryString + "upper (model."+ at + ") LIKE upper ('"+val.toString()+"%') ";	
						}
					}else{
						if(i==2){
							if(condicionFiltro==2){
								queryString = queryString + val + " BETWEEN model.medicamentos.edadmin AND model.medicamentos.edadmax";
							}else{
								queryString = queryString + val + " BETWEEN model.edadmin AND model.edadmax";
							}
							
						}else{
							queryString = queryString + " model.id.ciaopr = '1' and model."+ at + " LIKE '"+val.toString()+"%'  ";	
						}
					}					
				}else if(TipoBusqueda.LIKE_CONTENGA_A == tipoBusqueda){
					if (aplicandoUpper){
						if(i==2){
							if(condicionFiltro==2){
								queryString = queryString + val + " BETWEEN model.medicamentos.edadmin AND model.medicamentos.edadmax";
							}else{
								queryString = queryString + val + " BETWEEN model.edadmin AND model.edadmax";
								
							}
						}else{
							queryString = queryString + "upper (model."+ at + ") LIKE upper ('%"+val.toString()+"%') ";	
						}	
					}else{
						if(i==2){
							if(condicionFiltro==2){
								queryString = queryString + val + " BETWEEN model.medicamentos.edadmin AND model.medicamentos.edadmax";
							}else{
								queryString = queryString + val + " BETWEEN model.edadmin AND model.edadmax";
							}
						}else{
								queryString = queryString + " model.id.ciaopr = '1' and  model."+ at + " LIKE '%"+val.toString()+"%' ";	
						}
								
					}			
				}else if(TipoBusqueda.OPE_IN == tipoBusqueda){
					if (aplicandoUpper){
						queryString = queryString + "upper (model."+ at + ") LIKE upper ('%"+val.toString()+"%') ";		
					}else{
						queryString = queryString + " model.id.ciaopr = '1' and  model."+ at + " LIKE '%"+val.toString()+"%' ";		
					}	
				}else{
					//Por defecto busca con el operador igual..
					if (aplicandoUpper){
						if(i==2){
							if(condicionFiltro==2){
								queryString = queryString + val + " BETWEEN model.medicamentos.edadmin AND model.medicamentos.edadmax";
							}else{
								queryString = queryString + val + " BETWEEN model.edadmin AND model.edadmax";
								
							}
						}else{
							queryString = queryString + "upper (model."+ at + ") = upper ('"+val.toString()+"') ";	
						}
							
					}else{
						if(i==2){
							if(condicionFiltro==2){
								queryString = queryString + val + " BETWEEN model.medicamentos.edadmin AND model.medicamentos.edadmax";
							}else{
								queryString = queryString + val + "  BETWEEN model.edadmin AND model.edadmax ";
								
							}
						}else{
							queryString = queryString + " model."+ at + " = '"+val.toString()+"' ";	
						}
						
					}			
				}
				
				if(i==1){
					queryString = queryString +") ";
				}	
			}else {
				//es Integer o Double, OJO: verificar el Double..
				if(i==2){
					if(condicionFiltro==2){
						queryString = queryString + val + " BETWEEN model.medicamentos.edadmin AND model.medicamentos.edadmax";
					}else{
						queryString = queryString + val + " BETWEEN model.edadmin AND model.edadmax";
					}
				}else{
					queryString = queryString + "model."+ at + " = "+val+" ";	
				}			
			}

			if ((i+1)!= atributo.length ){
				//hay mas de uno..
				System.out.println("i: " + i);
				if(i==0){
					if(condicionFiltro==2){
						queryString = queryString + " OR ";
					}else{
						if(i+2 >= atributo.length){
							queryString = queryString + " AND ";
						}else{
							queryString = queryString + " OR ";
						}
						
					}
				}else{
					queryString = queryString + " AND ";	
				}
					
			}
			
		}
		
		aplicandoUpper = false; // por si acaso
		return queryString;
	}

	private String armarQuery3(String[] atributo, Object[] valor, int tipoBusqueda, int condicionFiltro, boolean ... aplicarUpper){
		if (aplicarUpper != null && aplicarUpper.length > 0){
			aplicandoUpper = aplicarUpper[0];
		}
		String queryString = "from "+clase.getSimpleName()+" as model where ";
		for (int i = 0; i < atributo.length; i++) {
			String at = atributo[i];
			Object val = valor[i];
			Object valTemp2 = valor[2];
			//System.out.println("i: " + i);
			if (val instanceof String){	
				if(i==0){
					queryString = queryString +"( ";
				}
				if (TipoBusqueda.LIKE_EMPIECE_POR == tipoBusqueda){
					if (aplicandoUpper){
						if(i==2){
							if(condicionFiltro==3){
								queryString = queryString + val + " BETWEEN model.medicamentos.edadmin AND model.medicamentos.edadmax";
							}else{
								queryString = queryString + val + " BETWEEN model.edadmin AND model.edadmax";	
							}
						}else{	
							queryString = queryString + "upper (model."+ at + ") LIKE upper ('"+val.toString()+"%') ";	
						}
					}else{
						if(i==2){
							if(condicionFiltro==3){
								queryString = queryString + val + " BETWEEN model.medicamentos.edadmin AND model.medicamentos.edadmax";
							}else{
								queryString = queryString + val + " BETWEEN model.edadmin AND model.edadmax";
							}
							
						}else{
							queryString = queryString + " model.id.ciaopr = '1' and model."+ at + " LIKE '"+val.toString()+"%'  ";	
						}
					}					
				}else if(TipoBusqueda.LIKE_CONTENGA_A == tipoBusqueda){
					if (aplicandoUpper){
						if(i==2){
							if(condicionFiltro==3){
								queryString = queryString + val + " BETWEEN model.medicamentos.edadmin AND model.medicamentos.edadmax";
							}else{
								queryString = queryString + val + " BETWEEN model.edadmin AND model.edadmax";
								
							}
						}else{
							queryString = queryString + "upper (model."+ at + ") LIKE upper ('%"+val.toString()+"%') ";	
						}	
					}else{
						if(i==2){
							if(condicionFiltro==3){
								queryString = queryString + val + " BETWEEN model.medicamentos.edadmin AND model.medicamentos.edadmax";
							}else{
								queryString = queryString + val + " BETWEEN model.edadmin AND model.edadmax";
							}
						}else{
								queryString = queryString + " model.id.ciaopr = '1' and  model."+ at + " LIKE '%"+val.toString()+"%' ";	
						}
								
					}			
				}else if(TipoBusqueda.OPE_IN == tipoBusqueda){
					if (aplicandoUpper){
						queryString = queryString + "upper (model."+ at + ") LIKE upper ('%"+val.toString()+"%') ";		
					}else{
						queryString = queryString + " model.id.ciaopr = '1' and  model."+ at + " LIKE '%"+val.toString()+"%' ";		
					}	
				}else{
					//Por defecto busca con el operador igual..
					if (aplicandoUpper){
						if(i==2){
							if(condicionFiltro==3){
								queryString = queryString + val + " BETWEEN model.medicamentos.edadmin AND model.medicamentos.edadmax";
							}else{
								queryString = queryString + val + " BETWEEN model.edadmin AND model.edadmax";
								
							}
						}else{
							queryString = queryString + "upper (model."+ at + ") = upper ('"+val.toString()+"') ";	
						}
							
					}else{
						if(i==2){
							if(condicionFiltro==3){
								queryString = queryString + val + " BETWEEN model.medicamentos.edadmin AND model.medicamentos.edadmax";
							}else{
								queryString = queryString + val + "  BETWEEN model.edadmin AND model.edadmax ";
								
							}
						}else{
							queryString = queryString + " model."+ at + " = '"+val.toString()+"' ";	
						}
						
					}			
				}
				
				if(i==1){
					queryString = queryString +") ";
				}	
			}else {
				//es Integer o Double, OJO: verificar el Double..
				if(i==2){
					if(condicionFiltro==3){
						queryString = queryString + val + " BETWEEN model.medicamentos.edadmin AND model.medicamentos.edadmax";
					}else{
						queryString = queryString + val + " BETWEEN model.edadmin AND model.edadmax";
					}
				}else{
					queryString = queryString + "model."+ at + " = "+val+" ";	
				}			
			}

			if ((i+1)!= atributo.length ){
				//hay mas de uno..
				
				if(i==0){
					queryString = queryString + " OR ";
				}else{
					if(i==3 || i==4){
						queryString = queryString + " OR ";	
					}else{
						queryString = queryString + " AND ";
					}
						
				}
					
			}
			
		}
		
		aplicandoUpper = false; // por si acaso
		return queryString;
	}
	private String armarQueryCount(String[] atributo, Object[] valor, int tipoBusqueda, boolean ... aplicarUpper){
		if (aplicarUpper != null && aplicarUpper.length > 0){
			aplicandoUpper = aplicarUpper[0];
		}
		String queryString = "Select count(model) from "+clase.getSimpleName()+" as model where ";
		for (int i = 0; i < atributo.length; i++) {
			String at = atributo[i];
			Object val = valor[i];
			if (val instanceof String){	
			
				if (TipoBusqueda.LIKE_EMPIECE_POR == tipoBusqueda){
					if (aplicandoUpper){
						queryString = queryString + "upper (model."+ at + ") LIKE upper ('"+val.toString().trim()+"%') ";	
					}else{
						queryString = queryString + " model."+ at + " LIKE '"+val.toString().trim()+"%'  ";	
					}					
				}else if(TipoBusqueda.LIKE_CONTENGA_A == tipoBusqueda){
					if (aplicandoUpper){
						queryString = queryString + "upper (model."+ at + ") LIKE upper ('%"+val.toString()+"%') ";		
					}else{
						queryString = queryString + " model."+ at + " LIKE '%"+val.toString().trim()+"%' ";		
					}				
				}else{
					//Por defecto busca con el operador igual..
					if (aplicandoUpper){
						queryString = queryString + "upper (model."+ at + ") = upper ('"+val.toString().trim()+"') ";	
					}else{
						queryString = queryString + " model."+ at + " = '"+val.toString().trim()+"' ";
					}			
				}
				
				
			}else {
				//es Integer o Double, OJO: verificar el Double..
				queryString = queryString + "model."+ at + " = "+val+" ";				
			}
			
			if ((i+1)!= atributo.length ){
				//hay mas de uno..
				queryString = queryString + " AND ";
			}
		}
		
		aplicandoUpper = false; // por si acaso
		return queryString;
	}

	
	private String armarQueryCount(String[] atributo, Object[] valor, int tipoBusqueda, int condicionFiltro, boolean ... aplicarUpper){
		if (aplicarUpper != null && aplicarUpper.length > 0){
			aplicandoUpper = aplicarUpper[0];
		}
		
		String queryString = "Select count(model) from "+clase.getSimpleName()+" as model where ";
		for (int i = 0; i < atributo.length; i++) {
			String at = atributo[i];
			Object val = valor[i];
			Object valTemp2 = valor[2];
			if (val instanceof String){	
				if(i==0){
					queryString = queryString +"( ";
				}
				
				if (TipoBusqueda.LIKE_EMPIECE_POR == tipoBusqueda){
					if (aplicandoUpper){
						if(i==2){
							if(condicionFiltro==2){
								queryString = queryString + val + " BETWEEN model.medicamentos.edadmin AND model.medicamentos.edadmax";
							}else{
								queryString = queryString + val + " BETWEEN model.edadmin AND model.edadmax";
								
							}
						}else{
							queryString = queryString + "upper (model."+ at + ") LIKE upper ('"+val.toString().trim()+"%') ";	
						}
					}else{
						if(i==2){
							if(condicionFiltro==2){
								queryString = queryString + val + " BETWEEN model.medicamentos.edadmin AND model.medicamentos.edadmax";
							}else{
								queryString = queryString + val + " BETWEEN model.edadmin AND model.edadmax";
								
							}
						}else{
							queryString = queryString + " model."+ at + " LIKE '"+val.toString().trim()+"%'  ";	
						}
					}					
				}else if(TipoBusqueda.LIKE_CONTENGA_A == tipoBusqueda){
					if (aplicandoUpper){
						if(i==2){
							if(condicionFiltro==2){
								queryString = queryString + val + " BETWEEN model.medicamentos.edadmin AND model.medicamentos.edadmax";
							}else{
								queryString = queryString + val + " BETWEEN model.edadmin AND model.edadmax";
								
							}
						}else{
							queryString = queryString + "upper (model."+ at + ") LIKE upper ('%"+val.toString()+"%') ";	
						}
					}else{
						if(i==2){
							if(condicionFiltro==2){
								queryString = queryString + val + " BETWEEN model.medicamentos.edadmin AND model.medicamentos.edadmax";
							}else{
								queryString = queryString + val + " BETWEEN model.edadmin AND model.edadmax";
								
							}
						}else{
							queryString = queryString + " model."+ at + " LIKE '%"+val.toString().trim()+"%' ";
						}
					}				
				}else if(TipoBusqueda.OPE_IN == tipoBusqueda){
					if (aplicandoUpper){
						if(i==2){
							if(condicionFiltro==2){
								queryString = queryString + val + " BETWEEN model.medicamentos.edadmin AND model.medicamentos.edadmax";
							}else{
								queryString = queryString + val + " BETWEEN model.edadmin AND model.edadmax";
							}
						}else{
							queryString = queryString + "upper (model."+ at + ") LIKE upper ('%"+val.toString()+"%') ";	
						}
								
					}else{
						if(i==2){
							if(condicionFiltro==2){
								queryString = queryString + val + " BETWEEN model.medicamentos.edadmin AND model.medicamentos.edadmax";
							}else{
								queryString = queryString + val + " BETWEEN model.edadmin AND model.edadmax";
								
							}

						}else{
							queryString = queryString + " model."+ at + " LIKE '%"+val.toString().trim()+"%' ";
						}
					}	
				}else{
					//Por defecto busca con el operador igual..
					if (aplicandoUpper){
						if(i==2){
							if(condicionFiltro==2){
								queryString = queryString + val + " BETWEEN model.medicamentos.edadmin AND model.medicamentos.edadmax";
							}else{
								queryString = queryString + val + " BETWEEN model.edadmin AND model.edadmax";
								
							}
						}else{
							queryString = queryString + "upper (model."+ at + ") = upper ('"+val.toString().trim()+"') ";	
						}
					}else{
						if(i==2){
							if(condicionFiltro==2){
								queryString = queryString + val + " BETWEEN model.medicamentos.edadmin AND model.medicamentos.edadmax";
							}else{
								queryString = queryString + val + " BETWEEN model.edadmin AND model.edadmax";
								
							}
						}else{
							queryString = queryString + " model."+ at + " = '"+val.toString().trim()+"' ";
							
						}
					}			
				}
				
				if(i==1){
					queryString = queryString +") ";
				}
			}else {
				//es Integer o Double, OJO: verificar el Double..
				if(i==2){
					if(condicionFiltro==2){
						queryString = queryString + val + " BETWEEN model.medicamentos.edadmin AND model.medicamentos.edadmax";
					}else{
						queryString = queryString + val + " BETWEEN model.edadmin AND model.edadmax";	
					}
					
				}else{
					queryString = queryString + "model."+ at + " = "+val+" ";	
				}
								
			}
			

			if ((i+1)!= atributo.length ){
				//hay mas de uno..
				
				if(i==0){
					queryString = queryString + " OR ";
				}else{
					queryString = queryString + " AND ";	
				}
					
			}
			
		}
		
		aplicandoUpper = false; // por si acaso
		return queryString;
	}


	private String armarQueryCountEspecialMedicamento(String[] atributo, Object[] valor, int tipoBusqueda, int condicionFiltro, boolean ... aplicarUpper){
		if (aplicarUpper != null && aplicarUpper.length > 0){
			aplicandoUpper = aplicarUpper[0];
		}
		
		String queryString = "Select count(model) from "+clase.getSimpleName()+" as model where ";
		for (int i = 0; i < atributo.length; i++) {
			String at = atributo[i];
			Object val = valor[i];
			Object valTemp2 = valor[2];
			if (val instanceof String){	
				if(i==0){
					queryString = queryString +"( ";
				}
				
				if (TipoBusqueda.LIKE_EMPIECE_POR == tipoBusqueda){
					if (aplicandoUpper){
						if(i==2){
							if(condicionFiltro==3){
								queryString = queryString + val + " BETWEEN model.medicamentos.edadmin AND model.medicamentos.edadmax";
							}else{
								queryString = queryString + val + " BETWEEN model.edadmin AND model.edadmax";
								
							}
						}else{
							queryString = queryString + "upper (model."+ at + ") LIKE upper ('"+val.toString().trim()+"%') ";	
						}
					}else{
						if(i==2){
							if(condicionFiltro==3){
								queryString = queryString + val + " BETWEEN model.medicamentos.edadmin AND model.medicamentos.edadmax";
							}else{
								queryString = queryString + val + " BETWEEN model.edadmin AND model.edadmax";
								
							}
						}else{
							queryString = queryString + " model."+ at + " LIKE '"+val.toString().trim()+"%'  ";	
						}
					}					
				}else if(TipoBusqueda.LIKE_CONTENGA_A == tipoBusqueda){
					if (aplicandoUpper){
						if(i==2){
							if(condicionFiltro==3){
								queryString = queryString + val + " BETWEEN model.medicamentos.edadmin AND model.medicamentos.edadmax";
							}else{
								queryString = queryString + val + " BETWEEN model.edadmin AND model.edadmax";
								
							}
						}else{
							queryString = queryString + "upper (model."+ at + ") LIKE upper ('%"+val.toString()+"%') ";	
						}
					}else{
						if(i==2){
							if(condicionFiltro==2){
								queryString = queryString + val + " BETWEEN model.medicamentos.edadmin AND model.medicamentos.edadmax";
							}else{
								queryString = queryString + val + " BETWEEN model.edadmin AND model.edadmax";
								
							}
						}else{
							queryString = queryString + " model."+ at + " LIKE '%"+val.toString().trim()+"%' ";
						}
					}				
				}else if(TipoBusqueda.OPE_IN == tipoBusqueda){
					if (aplicandoUpper){
						if(i==2){
							if(condicionFiltro==3){
								queryString = queryString + val + " BETWEEN model.medicamentos.edadmin AND model.medicamentos.edadmax";
							}else{
								queryString = queryString + val + " BETWEEN model.edadmin AND model.edadmax";
							}
						}else{
							queryString = queryString + "upper (model."+ at + ") LIKE upper ('%"+val.toString()+"%') ";	
						}
								
					}else{
						if(i==2){
							if(condicionFiltro==3){
								queryString = queryString + val + " BETWEEN model.medicamentos.edadmin AND model.medicamentos.edadmax";
							}else{
								queryString = queryString + val + " BETWEEN model.edadmin AND model.edadmax";
								
							}

						}else{
							queryString = queryString + " model."+ at + " LIKE '%"+val.toString().trim()+"%' ";
						}
					}	
				}else{
					//Por defecto busca con el operador igual..
					if (aplicandoUpper){
						if(i==2){
							if(condicionFiltro==3){
								queryString = queryString + val + " BETWEEN model.medicamentos.edadmin AND model.medicamentos.edadmax";
							}else{
								queryString = queryString + val + " BETWEEN model.edadmin AND model.edadmax";
								
							}
						}else{
							queryString = queryString + "upper (model."+ at + ") = upper ('"+val.toString().trim()+"') ";	
						}
					}else{
						if(i==2){
							if(condicionFiltro==3){
								queryString = queryString + val + " BETWEEN model.medicamentos.edadmin AND model.medicamentos.edadmax";
							}else{
								queryString = queryString + val + " BETWEEN model.edadmin AND model.edadmax";
								
							}
						}else{
							queryString = queryString + " model."+ at + " = '"+val.toString().trim()+"' ";
							
						}
					}			
				}
				
				if(i==1){
					queryString = queryString +") ";
				}
			}else {
				//es Integer o Double, OJO: verificar el Double..
				if(i==2){
					if(condicionFiltro==3){
						queryString = queryString + val + " BETWEEN model.medicamentos.edadmin AND model.medicamentos.edadmax";
					}else{
						queryString = queryString + val + " BETWEEN model.edadmin AND model.edadmax";	
					}
					
				}else{
					queryString = queryString + "model."+ at + " = "+val+" ";	
				}
								
			}
			

			if ((i+1)!= atributo.length ){
				//hay mas de uno..
				
				if(i==0){
					queryString = queryString + " OR ";
				}else{
					if(i==3 || i==4){
						queryString = queryString + " OR ";
					}else{
						queryString = queryString + " AND ";	
					}
						
				}
					
			}
			
		}
		
		aplicandoUpper = false; // por si acaso
		return queryString;
	}
	
	private String armarQueryMultiPase(String[] atributo, Object[] valor, int[] tipoBusqueda, boolean ... aplicarUpper){
		if (aplicarUpper != null && aplicarUpper.length > 0){
			aplicandoUpper = aplicarUpper[0];
		}
		String queryString = "from "+clase.getSimpleName()+" as model where ";
		for (int i = 0; i < atributo.length; i++) {
			String at = atributo[i];
			Object val = valor[i];
			int op = tipoBusqueda[i];
			if (val instanceof String){	
			
				if (TipoBusqueda.LIKE_EMPIECE_POR == op){
					if (aplicandoUpper){
						queryString = queryString + "upper (model."+ at + ") LIKE upper (?) ";	
					}else{
						queryString = queryString + " model.id.ciaopr = '1' and model."+ at + " LIKE ?  ";	
					}					
					String temp = val.toString() + "%";
					valor[i] = temp;
				}else if(TipoBusqueda.LIKE_CONTENGA_A == op){
					if (aplicandoUpper){
						queryString = queryString + "upper (model."+ at + ") LIKE upper (?) ";		
					}else{
						queryString = queryString + " model.id.ciaopr = '1' and  model."+ at + " LIKE ? ";		
					}				
					String temp = "%"+val.toString() + "%";
					valor[i] = temp;							
				}else{
					//Por defecto busca con el operador igual..
					if (aplicandoUpper){
						queryString = queryString + "upper (model."+ at + ") = upper (?) ";	
					}else{
						queryString = queryString + " model."+ at + " = ? ";
					}			
				}
				
				
			}else {
				//es Integer o Double, OJO: verificar el Double..
				queryString = queryString + "model."+ at + " = ? ";				
			}
			
			if ((i+1)!= atributo.length ){
				//hay mas de uno..
				queryString = queryString + " AND ";
			}
		}
		
		aplicandoUpper = false; // por si acaso
		return queryString;
	}

	private String armarQueryMultiPaseCount(String[] atributo, Object[] valor, int[] tipoBusqueda, boolean ... aplicarUpper){
		if (aplicarUpper != null && aplicarUpper.length > 0){
			aplicandoUpper = aplicarUpper[0];
		}
		String queryString = "Select count(model) from "+clase.getSimpleName()+" as model where ";
		for (int i = 0; i < atributo.length; i++) {
			String at = atributo[i];
			Object val = valor[i];
			int op = tipoBusqueda[i];
			if (val instanceof String){	
			
				if (TipoBusqueda.LIKE_EMPIECE_POR == op){
					if (aplicandoUpper){
						queryString = queryString + "upper (model."+ at + ") LIKE upper (?) ";	
					}else{
						queryString = queryString + " model.id.ciaopr = '1' and model."+ at + " LIKE ?  ";	
					}					
					String temp = val.toString() + "%";
					valor[i] = temp;
				}else if(TipoBusqueda.LIKE_CONTENGA_A == op){
					if (aplicandoUpper){
						queryString = queryString + "upper (model."+ at + ") LIKE upper (?) ";		
					}else{
						queryString = queryString + " model.id.ciaopr = '1' and  model."+ at + " LIKE ? ";		
					}				
					String temp = "%"+val.toString() + "%";
					valor[i] = temp;							
				}else{
					//Por defecto busca con el operador igual..
					if (aplicandoUpper){
						queryString = queryString + "upper (model."+ at + ") = upper (?) ";	
					}else{
						queryString = queryString + " model."+ at + " = ? ";
					}			
				}
				
				
			}else {
				//es Integer o Double, OJO: verificar el Double..
				queryString = queryString + "model."+ at + " = ? ";				
			}
			
			if ((i+1)!= atributo.length ){
				//hay mas de uno..
				queryString = queryString + " AND ";
			}
		}
		
		aplicandoUpper = false; // por si acaso
		//System.out.println("queryString.armarQueryMultiPaseCount:: " + queryString);
		return queryString;
	}
	
	public List ejecutarQueryConsulta(String query) {
		try {
			return this.ejecutarQueryConsulta(query, null);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	
	/**
	 * Realiza una consulta recibiendo como parametros un query HQL y una variable {@link Session}.
	 * En el caso de que la variable sesion venga null, el metodo trata la consulta como una transaccion atomica, 
	 * abre una sesion mediante el {@link HibernateTemplate}, inicia una transaccion, realiza la consulta, si se ejecuta
	 * satisfactoriamente realiza un comit y cierra la sesion. En caso contrario hace rollback, cierra la sesion y lanza
	 * la excepcion para que sea manejada por el servicio. Si la variable sesion no viene null, realiza la consulta, si esta
	 * se realiza satisfactoriamente retorna una lista con los resultados que obtuvo, en caso contrario lanza la excepcion para
	 * que esta sea manejada por el servicio.
	 * @param query : String con la consulta HQL
	 * @param sesion : Variable {@link Session}
	 * @return {@link List}: resultQuery.
	 * @throws GeneralException
	 * @author Carlos Yepez.
	 */
	public List ejecutarQueryConsulta(String query,Session sesion) throws Exception {
		
		Session s = null; //variable de sesion
		Transaction t = null; //variable de transaccion: solo para ser usada en proceso atomico.
		List resultQuery = null; //List con los resultados que emita la consulta.
		
		if(sesion==null){//Si la sesion viene null
			s = getHibernateTemplate().getSessionFactory().openSession();//abro una sesion
			t = s.beginTransaction();//inicio una transaccion atomica
		}else{
			s = sesion;//en caso contrario usare la sesion que me viene por parametros.
		}
		try{
			
			Query q = s.createQuery(query);//creo la consulta
			resultQuery = q.list();//ejecuto la consulta.
			
			if(sesion == null){//si inicie la sesion en este metodo.
				t.commit();//hago commint
				s.close();//cierro la sesion
				t = null;//limpio la variable de transaccion
				s=null;//limpio la variable de sesion.
			}
			
		}catch (Exception e) {
			if(sesion==null){//Si inicie la sesion en este metodo
				t.rollback();//hago rollback
				s.close();//cierro la sesion
				t = null;//limpio la variable de transaccion
				s=null;//limpio la variable de sesion.
				throw e;//Lanzo la excepcion para que sea manejada por el servicio.
			}else{
				throw e;//Lanzo la excepcion para que sea manejada por el servicio.
			}//EoIf
		}//EoC
		return resultQuery; //Retorno el List con los resultados de la consulta.
	}//EoM
	

	/**
	 * Ejecuta una sentencia HQL, recibe como parametros dicha sentencia y un objeto {@link Session}.
	 * Si la sesion viene null, el metodo obtendra una sesion del {@link HibernateTemplate}, iniciara una
	 * transaccion, ejecutara la sentencia y hara un commit, tratando la operacion como una transaccion atomica.
	 * Si ocurriera alguna excepcion, realiza un rollback de la transaccion, cierra la sesion, y limplia las
	 * variables de sesion y de transaccion. En el caso que la sesion no sea null, ejecuta la sentencia usadio
	 * dicha sesion. Si ocurre alguna excepcion, la lanza para que sea manejada por el servicio.
	 * @param sentencia: String HQL
	 * @param sesion: {@link Session}
	 * @throws Exception
	 * @author Carlos Yepez.
	 */
	public void ejecutarQuerySession(String sentencia, org.hibernate.Session sesion) throws Exception{
		
		Session s = null; //variable de sesion
		Transaction t = null; //variable de transaccion: solo para ser usada en proceso atomico.

		if(sesion == null){//Si la sesion vino null
			s = getHibernateTemplate().getSessionFactory().openSession();//abro una sesion
			t = s.beginTransaction();//inicio una transaccion
		}else{
			s = sesion;//si la sesion es distinta de null, uso la que recibo por parametros.
		}
		try{
			Query query = s.createQuery(sentencia);//creo la consulta
			query.executeUpdate();//ejecuto
			if(sesion == null){//si en este metodo inicie la sesion
				t.commit();//hago commit
				s.close();//cierro la sesion que abri
				t = null;//limpio la variable de transaccion
				s = null;//limpio la varialbe de sesion
			}
		}catch (Exception e) {//Si ocurre alguna excepcion.
			if(sesion == null){//Si inicie la sesion en este metodo
				t.rollback();//hago rolback de la transaccion
				t = null;//limpio la variable de transaccion
				s = null;//limpio la variable de sesion
				throw e;//Lanzo la excepcion capturada.
			}else{//Si la sesion no fue iniciada en este metodo
				throw e; //Lanzo la excepcion al servicio.
			}//EoIf
		}//EoC
	}//EoM

		
	/**
	 * Permite ejecutar una consulta de tipo SQL consistente en un update.
	 * @param sqlQuery sql update query
	 * @param sesion sesion de hibernate
	 * @throws Exception
	 * @author Carlos Yepez.
	 */
	public void ejecutarSQLUpdate(String sqlQuery, Session sesion) throws Exception{
		System.err.println("********************* ejecutarSQLUpdate **************************");
		Session s = null;//Sesion con la que trabajare
		Transaction t = null;//transaccion con la que trabajare
		
		if(sesion == null){//si la sesion que recibo por parametros me viene null
			s = getSessionFactory().openSession();//abro una sesion
			t = s.beginTransaction();//inicio una transaccion
		}else{//si la sesion que recibo por parametros es distinto de null
			s = sesion;//entonces trabajo con la sesion que recibo por parametros
		}
		
		try{
//			SQLQuery query = s.createSQLQuery(sqlQuery);//creo  un objeto del tipo SQLQuery
//			query.executeUpdate();//ejecuto la consulta
			s.connection().prepareStatement(sqlQuery).execute();
			if(sesion==null){//si la sesion que viene por parametros es null
				t.commit();//trato la transsaccion atomicamente, e caso contrario, se debe hacer commit en una clase superior
				s.close();//cierro la sesion
				t = null;//limpio la transaccion
				s = null;//limpio la sesion
			}
		}catch (Exception e) {//si hay algun problema
			e.printStackTrace();
			if(sesion == null){//si la sesion que recibo por parametros es null
				t.rollback();//reverso la transaccion
				s.close();//cierro la sesion
				t = null;//limpio la transaccion
				s = null;//limpio la sesion
			}else{//si la sesion que recibo por parametros no es null
				throw e;//lanzo la excepcion para que sea manejado por la clase que llamo al metodo
			}
		}
	}
	
	public List ejecutarConsultaSQL(String sqlQuery, Session sesion) throws Exception{
		List resultado = null;
		Session     s  = null; //Sesion con la que trabajare
		Transaction t  = null; //transaccion con la que trabajare
		
		if(sesion == null){   //si la sesion que recibo por parametros me viene null
			s = getSessionFactory().openSession();//abro una sesion
			t = s.beginTransaction();//inicio una transaccion
		}else{//si la sesion que recibo por parametros es distinto de null
			s = sesion;//entonces trabajo con la sesion que recibo por parametros
		}
		
		try{
			SQLQuery query = s.createSQLQuery(sqlQuery);//creo un objeto del tipo SQLQuery
			resultado = query.list();
			if(sesion==null){//si la sesion que viene por parametros es null
				t.commit();//trato la transsaccion atomicamente, e caso contrario, se debe hacer commit en una clase superior
			}
			
		}catch (Exception e) {//si hay algun problema
			resultado = null;
			if(sesion == null){//si la sesion que recibo por parametros es null
				t.rollback();//reverso la transaccion
			}else{//si la sesion que recibo por parametros no es null
				throw e;//lanzo la excepcion para que sea manejado por la clase que llamo al metodo
			}
		}finally{
			if(sesion == null){
				s.close();//cierro la sesion
			}
			t = null;//limpio la transaccion
			s = null;//limpio la sesion
		}
		return resultado;
	}
	
	
}//EoC
